package hu.bme.orarend.server;
import hu.bme.orarend.shared.ClassDTO;
import hu.bme.orarend.shared.RoomDTO;
import hu.bme.orarend.shared.ScheduleDTO;

import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Time;
import java.util.ArrayList;
public class DBConnector {
	java.sql.Connection con;
	

  public DBConnector(){
    try{
      Class.forName("com.mysql.jdbc.Driver").newInstance();

      /**/
       }catch (Exception e){
      System.out.println("Error: "+e);
    }

    try{
        String dbUrl ="jdbc:mysql://localhost/schedule";
        String dbUser = "schedule";
        String dbPassword = "schedule";

        
        con=DriverManager.getConnection(dbUrl, dbUser, dbPassword);
        System.out.println("kapcsolat sikerült");

        
    }catch(SQLException ex){System.err.println("Error: "+ex.getMessage());}
  }
  public boolean saveRoom(RoomDTO room)
  {
	  try
	  {
		  PreparedStatement stmt=con.prepareStatement(" INSERT INTO room VALUES (?,?,?,?,?,?,?,?)");		
	      stmt.setString(1,room.getName());
	      stmt.setString(2,room.getBuilding());
	      stmt.setString(3,room.getFloor());
	      stmt.setString(4,room.getDoor());
	      stmt.setInt(5,room.getCapacity());
	      stmt.setString(6,room.getType());
	      stmt.setBoolean(7, room.getProjector());
	      stmt.setBoolean(8, room.getAmplifier());
	      stmt.execute();
	      
	      System.out.println("beillesztés sikerült");

		  return true;
	  }
	  catch (SQLException ex)
	  {
		  if (ex.getErrorCode()==1062)
		  {
			  return modifyRoom(room);
		  }
		  else
		  {
			  System.err.println("Error: "+ex.getMessage());
			  return false;
		  }
	  }
	  catch (Exception ex)
	  {
		  System.err.println("Error: "+ex.getMessage());
		  return false;
	  }
	  
	  

  }
  public boolean modifyRoom(RoomDTO room)
  {
	  try
	  {
		  PreparedStatement stmt=con.prepareStatement(" UPDATE room SET building=?,floor=?,door=?,capacity=?,type=?,projector=?,amplifier=? WHERE name=?");		

	      stmt.setString(1,room.getBuilding());
	      stmt.setString(2,room.getFloor());
	      stmt.setString(3,room.getDoor());
	      stmt.setInt(4,room.getCapacity());
	      stmt.setString(5,room.getType());
	      stmt.setBoolean(6, room.getProjector());
	      stmt.setBoolean(7, room.getAmplifier());
	      stmt.setString(8,room.getName());
	      stmt.execute();
	      
	      System.out.println("módosítás sikerült");

		  return true;
	  }
	  catch (SQLException ex)
	  {
		  System.err.println("Error: "+ex.getMessage());
		  return false;
	  }
	  catch (Exception ex)
	  {
		  System.err.println("Error: "+ex.getMessage());
		  return false;
	  }
	  
	  

  }

  public RoomDTO getRoom()
  {
	  try
	  {
		  RoomDTO room=null;
		  PreparedStatement stmt=con.prepareStatement("Select * from room");		
		  ResultSet rs=stmt.executeQuery();
		  while(rs.next())
		  {
			  room=new RoomDTO(rs.getString("name"), rs.getString("building"),rs.getString("floor"),rs.getString("door"),rs.getInt("capacity"),rs.getString("type"),rs.getBoolean("projector"),rs.getBoolean("amplifier"));
		  }
	      
	      System.out.println("beilleszt�s siker�lt");

		  return room;

	  }
	  catch (SQLException ex)
	  {
		  System.err.println("Error: "+ex.getMessage());
		  return null;
	  }
  }
  public ArrayList<RoomDTO> getRoomList()
  {
	  try
	  {
		  ArrayList<RoomDTO> roomList=new ArrayList<RoomDTO>();
		  PreparedStatement stmt=con.prepareStatement("Select * from room");		
		  ResultSet rs=stmt.executeQuery();
		  while(rs.next())
		  {
			  roomList.add(new RoomDTO(rs.getString("name"), rs.getString("building"),rs.getString("floor"),rs.getString("door"),rs.getInt("capacity"),rs.getString("type"),rs.getBoolean("projector"),rs.getBoolean("amplifier")));
			  System.out.println(rs.getString("name"));
		  }
	      
	      //System.out.println("terem lista lekérés sikerült");

		  return roomList;
	  }
	  catch (SQLException ex)
	  {
		  System.err.println("Error: "+ex.getMessage()+" Code: "+ex.getErrorCode());
		  return null;
	  }
  }
  public boolean saveClass(ClassDTO cl) throws Exception
  {
	  try
	  {
		  if(cl.getId()==0)
		  {
			  PreparedStatement stmt=con.prepareStatement(" INSERT INTO class (name, description, teacher, roomtype, number, projector, amplifier, start, end, day, room) VALUES (?,?,?,?,?,?,?,?,?,?,?)");		
		      stmt.setString(1,cl.getName());
		      stmt.setString(2,cl.getDescription());
		      stmt.setString(3,cl.getTeacher());
		      stmt.setString(4,cl.getRoomType());
		      stmt.setInt(5,cl.getNumber());
		      stmt.setBoolean(6,cl.isProjector());
		      stmt.setBoolean(7, cl.isAmplifier());
		      stmt.setTime(8, toTime(cl.getStart()));
		      stmt.setTime(9, toTime(cl.getEnd()));
		      stmt.setString(10, cl.getDay());
		      stmt.setString(11,cl.getRoom());
		      stmt.execute();
		      
		      System.out.println("beillesztés sikerült");
		  }
		  else
		  {
			  PreparedStatement stmt=con.prepareStatement(" UPDATE class SET name=?, description=?, teacher=?, roomtype=?, number=?, projector=?, amplifier=?, start=?, end=?, room=?, day=? where id=?");		
		      stmt.setString(1,cl.getName());
		      stmt.setString(2,cl.getDescription());
		      stmt.setString(3,cl.getTeacher());
		      stmt.setString(4,cl.getRoomType());
		      stmt.setInt(5,cl.getNumber());
		      stmt.setBoolean(6,cl.isProjector());
		      stmt.setBoolean(7, cl.isAmplifier());
		      stmt.setTime(8, toTime(cl.getStart()));
		      stmt.setTime(9, toTime(cl.getEnd()));
		      stmt.setString(10,cl.getRoom());
		      stmt.setString(11, cl.getDay());
		      stmt.setInt(12,cl.getId());
		      stmt.execute();
		      
		      System.out.println("módosítás sikerült");
		  }

		  return true;
	  }
	  catch (SQLException ex)
	  {
		  System.err.println("Error: "+ex.getMessage());
		  return false;
	  }
	  catch (Exception ex)
	  {
		  System.err.println("Error: "+ex.getMessage());
		  throw ex;
	  }
  }
  public ArrayList<ClassDTO> getClassList()
  {
	  try
	  {
		  ArrayList<ClassDTO> classList=new ArrayList<ClassDTO>();
		  PreparedStatement stmt=con.prepareStatement("Select * from class");		
		  ResultSet rs=stmt.executeQuery();
		  while(rs.next())
		  {
			  ClassDTO cl=new ClassDTO();
			  cl.setId(rs.getInt("id"));
			  cl.setName(rs.getString("name"));
			  cl.setDescription(rs.getString("description"));
			  cl.setNumber(rs.getInt("number"));
			  cl.setRoomType(rs.getString("roomtype"));
			  cl.setTeacher(rs.getString("teacher"));
			  cl.setProjector(rs.getBoolean("projector"));
			  cl.setAmplifier(rs.getBoolean("amplifier"));
			  cl.setStart(rs.getTime("start").toString());
			  cl.setEnd(rs.getTime("end").toString());
			  cl.setDay(rs.getString("day"));
			  cl.setRoom(rs.getString("room"));
			  
			  classList.add(cl);
			  
			  System.out.println(rs.getString("name"));
		  }
	      
	      //System.out.println("terem lista lekérés sikerült");

		  return classList;
	  }
	  catch (SQLException ex)
	  {
		  System.err.println("Error: "+ex.getMessage()+" Code: "+ex.getErrorCode());
		  return null;
	  }
  }
 
  public ArrayList<ClassDTO> getLeftClassList()
  {
	  try
	  {
		  ArrayList<ClassDTO> classList=new ArrayList<ClassDTO>();
		  PreparedStatement stmt=con.prepareStatement("Select * from class where id not in (select idclass from schedule)");		
		  ResultSet rs=stmt.executeQuery();
		  while(rs.next())
		  {
			  ClassDTO cl=new ClassDTO();
			  cl.setId(rs.getInt("id"));
			  cl.setName(rs.getString("name"));
			  cl.setDescription(rs.getString("description"));
			  cl.setNumber(rs.getInt("number"));
			  cl.setRoomType(rs.getString("roomtype"));
			  cl.setTeacher(rs.getString("teacher"));
			  cl.setProjector(rs.getBoolean("projector"));
			  cl.setAmplifier(rs.getBoolean("amplifier"));
			  cl.setStart(rs.getTime("start").toString());
			  cl.setEnd(rs.getTime("end").toString());
			  cl.setDay(rs.getString("day"));
			  cl.setRoom(rs.getString("room"));
			  
			  classList.add(cl);
			  
			  System.out.println(rs.getString("name"));
		  }
	      
	      //System.out.println("terem lista lekérés sikerült");

		  return classList;
	  }
	  catch (SQLException ex)
	  {
		  System.err.println("Error: "+ex.getMessage()+" Code: "+ex.getErrorCode());
		  return null;
	  }
  }

  public ArrayList<ScheduleDTO> getScheduleList()
  {
	  try
	  {
		  ArrayList<ScheduleDTO> scheduleList=new ArrayList<ScheduleDTO>();
		  PreparedStatement stmt=con.prepareStatement("Select * from class, room, schedule where schedule.idclass=class.id and schedule.nameroom=room.name");		
		  ResultSet rs=stmt.executeQuery();
		  while(rs.next())
		  {
			  ClassDTO cl=new ClassDTO();
			  RoomDTO room=new RoomDTO();
			  cl.setId(rs.getInt("id"));
			  cl.setName(rs.getString("name"));
			  cl.setDescription(rs.getString("description"));
			  cl.setNumber(rs.getInt("number"));
			  cl.setRoomType(rs.getString("roomtype"));
			  cl.setTeacher(rs.getString("teacher"));
			  cl.setProjector(rs.getBoolean("projector"));
			  cl.setAmplifier(rs.getBoolean("amplifier"));
			  cl.setStart(rs.getTime("start").toString());
			  cl.setEnd(rs.getTime("end").toString());
			  cl.setDay(rs.getString("day"));
			  
			  room=new RoomDTO(rs.getString("name"), rs.getString("building"),rs.getString("floor"),rs.getString("door"),rs.getInt("capacity"),rs.getString("type"),rs.getBoolean("projector"),rs.getBoolean("amplifier"));

			  ScheduleDTO sch=new ScheduleDTO();
			  sch.setCl(cl);
			  sch.setRoom(room);
			  scheduleList.add(sch);
			  
			  System.out.println(rs.getString("name"));
		  }
	      
	      //System.out.println("terem lista lekérés sikerült");

		  return scheduleList;
	  }
	  catch (SQLException ex)
	  {
		  System.err.println("Error: "+ex.getMessage()+" Code: "+ex.getErrorCode());
		  return null;
	  }
  }
  private Time toTime(String s) throws Exception
  {
	  try
	  {
		  int h,m;
		  String[] time=s.split(":");
		  h= Integer.valueOf(time[0]);
		  m= Integer.valueOf(time[1]);
		  return new Time(h,m,0);
	  }
	  catch(Exception e)
	  {
		  throw new Exception("Rossz időformátum");
	  }
  }
  public int createSchedule()
  {
	  ArrayList<RoomDTO> roomlist=getRoomList();
	  ArrayList<ClassDTO> classlist=getClassList();
	  ArrayList<ScheduleDTO> schedulelist=new ArrayList<ScheduleDTO>();
	  int classsize = classlist.size();
	  int roomsize=roomlist.size();
	  try
	  {
		  PreparedStatement stmt=con.prepareStatement(" delete from schedule ");	
		  stmt.execute();
	  }
	  catch (SQLException e)
	  {
		  System.out.println("Error: "+e.getMessage());
	  }
	  
	  for(int i=0;i<classsize;i++)
	  {
		  ClassDTO cl=classlist.get(i);
		  RoomDTO actualroom=null;
		  //megkeresi a legkisebb neki megfelelő termet
		  for(int j=0;j<roomsize;j++)
		  {
			  RoomDTO room=roomlist.get(j);
			  if((isAvailable(room,cl , schedulelist))&&(room.getCapacity()>=cl.getNumber())&&(room.getType().equals(cl.getRoomType()))&&(room.getProjector()==cl.isProjector())&&(room.getAmplifier()==cl.isAmplifier()))
			  {
				  if(actualroom==null)
				  {
					  actualroom=room;
				  }
				  else if(actualroom.getCapacity()>room.getCapacity())
				  {
					  actualroom=room;
				  }
			  }
		  }
		  if(actualroom!=null)
		  {
			  ScheduleDTO sch=new ScheduleDTO();
			  sch.setCl(cl);
			  sch.setRoom(actualroom);
			  schedulelist.add(sch);
			  System.out.println("összerendelve: "+actualroom.getName()+" és "+cl.getId());
		  
			  try
			  {
				  PreparedStatement stmt=con.prepareStatement(" INSERT INTO schedule (idclass, nameRoom) VALUES (?,?)");	
				  stmt.setInt(1, cl.getId());
			      stmt.setString(2,actualroom.getName());
			      stmt.execute();
			  }
			  catch (SQLException ex)
			  {
					  System.err.println("Error: "+ex.getMessage());
			  }
		  }

	  }
	  return 0;
  }
  
  private boolean isAvailable(RoomDTO room, ClassDTO cl, ArrayList<ScheduleDTO> schedulelist)
  {
	  try
	  {
		  Time start=toTime(cl.getStart());
		  Time end=toTime(cl.getEnd());
	  
		/*  PreparedStatement stmt=con.prepareStatement("Select * from class where id=(Select idclass from schedule where name=?)");	
		  stmt.setString(1, room.getName());
		  ResultSet rs=stmt.executeQuery();*/
		  int schedulesize=schedulelist.size();
		  
		  for (int i=0;i<schedulesize;i++)
		  {
			  //csak erre a szobára kereső adatokat nézzük
			  if ( schedulelist.get(i).getRoom() == room)
			  {
				  //ennek a szobának a többi osztálya
				  ClassDTO cl2 = schedulelist.get(i).getCl();
				  //ha belelóg abba az órába
				  if((cl.getDay().equals(cl2.getDay()))&&(((start.compareTo(toTime(cl2.getStart()))>0)&&(start.compareTo(toTime(cl2.getEnd()))<0))||((end.compareTo(toTime(cl2.getStart()))>0)&&(end.compareTo(toTime(cl2.getEnd()))<0))))
				  {
					  return false;
				  }
			  }
			  
			  
		  }
		  return true;
	  }
	  catch (Exception e)
	  {
		  System.out.println(" Error: "+e.getMessage());
	  }
	  return false;
  }
}
